package org.compiere.process;

import java.io.File;
import java.sql.Timestamp;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MCity;
import org.compiere.model.MCountry;
import org.compiere.model.MCurrency;
import org.compiere.model.MTax;
import org.compiere.model.Mhrmtripnormativeline;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class GenerateBusinessTrip extends SvrProcess {
	
	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** destination city_id */
	private int hrm_whereto1_ID;
	private int hrm_whereto2_ID;
	private int hrm_whereto3_ID;
	private int hrm_whereto4_ID;
	private int hrm_whereto_ID;
	/** */
	private String path = "";
	/** departure date*/
	private Timestamp hrm_days1_ID = null;
	private Timestamp hrm_days2_ID = null;
	private Timestamp hrm_days3_ID = null;
	private Timestamp hrm_days4_ID = null;
	private Timestamp hrm_days5_ID = null;
	
	@Override
	protected void prepare() {
		
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
				
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null);
			else if (name.compareTo("hrm_whereto1_ID") == 0)
				hrm_whereto1_ID = Integer.parseInt(para[i].getParameter().toString());
			else if (name.compareTo("hrm_days1_ID") == 0)
				hrm_days1_ID = (Timestamp) para[i].getParameter();
			else if (name.compareTo("hrm_whereto2_ID") == 0)
				hrm_whereto2_ID = Integer.parseInt(para[i].getParameter().toString());
			else if (name.compareTo("hrm_days2_ID") == 0)
				hrm_days2_ID = (Timestamp) para[i].getParameter();
			else if (name.compareTo("hrm_whereto3_ID") == 0)
				hrm_whereto3_ID = Integer.parseInt(para[i].getParameter().toString());
			else if (name.compareTo("hrm_days3_ID") == 0)
				hrm_days3_ID = (Timestamp) para[i].getParameter();
			else if (name.compareTo("hrm_whereto4_ID") == 0)
				hrm_whereto4_ID = Integer.parseInt(para[i].getParameter().toString());
			else if (name.compareTo("hrm_days4_ID") == 0)
				hrm_days4_ID = (Timestamp) para[i].getParameter();
			else if (name.compareTo("hrm_whereto_ID") == 0)
				hrm_whereto_ID = Integer.parseInt(para[i].getParameter().toString());
			else if (name.compareTo("hrm_days5_ID") == 0)
				hrm_days5_ID = (Timestamp) para[i].getParameter();
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter() + System.getProperty("file.separator");
			else
			{
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
			}
		}	
	
	}

	@Override
	protected String doIt() throws Exception {
		
		// Types of cells for
		Label label;
		DateTime dateCell;
		Number number;
		Formula formula;
		MCity city = null;
		MCountry country = null;
			
		int[] whereToArray = {hrm_whereto1_ID,hrm_whereto2_ID,hrm_whereto3_ID,hrm_whereto4_ID,hrm_whereto_ID};
		Timestamp[] daysDate = {hrm_days1_ID,hrm_days2_ID,hrm_days3_ID,hrm_days4_ID,hrm_days5_ID};
		// There are checks for a specified pattern
		String templateName = "SampleTripCalc";
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
		
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("Trip calc").append(fileExtension);
		
		// 
        File templateCopy = new File(fullPath.toString());
        File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
        } catch (Exception e) {
        	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
        	return "Error tableBook.";
        }

       ExcelCell cellStart1 =  Util.getCellStart(tableBook,">>");
 					
       // Standart style cell
       WritableCellFormat borderStyle = new WritableCellFormat();
       borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
       borderStyle.setAlignment(Alignment.CENTRE);
       borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       borderStyle.setWrap(true);
       //
       DateFormat customDateFormat = new DateFormat ("dd.MM.yyyy");
       WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);
       dateFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
       dateFormat.setAlignment(Alignment.CENTRE);
       dateFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
       //
       
       int days24 = 0;
       int currency = 0;
       int index = 0;
       Timestamp firstdt = null;
       Timestamp lastdt = null;
           
       // First row must be zero
       firstdt = daysDate[0];
       city = MCity.get(getCtx(), whereToArray[0]);
       country = MCountry.get(getCtx(), city.getC_Country_ID());
	   // Fill constants cells (USD, EUR and MRP values)
	   currency  = MCurrency.getCurrencyRate("USD", firstdt);
	   number = new Number(cellStart1.getC()+7, cellStart1.getR()-1, currency, borderStyle);
	   sheet.addCell(number);
	   currency  = MCurrency.getCurrencyRate("EUR", firstdt);
	   number = new Number(cellStart1.getC()+8, cellStart1.getR()-1, currency, borderStyle);
	   sheet.addCell(number);
	   int rate = MTax.getRateinName(country.getC_Country_ID(), "MRP", firstdt);
	   number = new Number(cellStart1.getC()+9, cellStart1.getR()-1, rate, borderStyle);
	   sheet.addCell(number);
	  
	   // first row, always content zero
       label  = new Label(cellStart1.getC(), cellStart1.getR(), city.getName(), borderStyle);
       sheet.addCell(label);
       dateCell = new DateTime(cellStart1.getC()+1, cellStart1.getR(), firstdt, dateFormat); 
       sheet.addCell(dateCell);
       number = new Number(cellStart1.getC()+2, cellStart1.getR(), 0, borderStyle);
	   sheet.addCell(number);
	   number = new Number(cellStart1.getC()+3, cellStart1.getR(), 0, borderStyle);
	   sheet.addCell(number);
	   number = new Number(cellStart1.getC()+4, cellStart1.getR(), 0, borderStyle);
	   sheet.addCell(number);
	   number = new Number(cellStart1.getC()+5, cellStart1.getR(), 0, borderStyle);
	   sheet.addCell(number);
	   
	   // shift the basement
 	   sheet.insertRow(cellStart1.getR()+1);
 	   int j = 8;
	   int n = 1;
	   Mhrmtripnormativeline hrmtrip = null;
	   //
       for(int i = 1; i < whereToArray.length; i++){
    	   if(whereToArray[i] != 0 && daysDate[i] != null){
   	    	   city = MCity.get(getCtx(), whereToArray[i]);
   	    	   country = MCountry.get(getCtx(), city.getC_Country_ID());
   	    		lastdt = daysDate[i];
	    		days24 = Util.getDays(firstdt,lastdt);
	    		firstdt = lastdt;
	    		if(country.getCountryCode().equals("KZ")){
	    			label  = new Label(cellStart1.getC(), cellStart1.getR()+n, city.getName(), borderStyle);	    			
	    		}else{
	    			label  = new Label(cellStart1.getC(), cellStart1.getR()+n, city.getName() + "," + country.getName(), borderStyle);
	    		}
	    	       sheet.addCell(label);
	    	       dateCell = new DateTime(cellStart1.getC()+1, cellStart1.getR()+n, daysDate[i], dateFormat); 
	    	       sheet.addCell(dateCell);
	    	       number = new Number(cellStart1.getC()+2, cellStart1.getR()+n, days24, borderStyle);
		    	   sheet.addCell(number);
		    	  
		    	   /** Define witch tariff used for country */
			    	  if(country.getCountryCode().equals("KZ")){
			    		  //perdiemKZ = 2 * rate;
			    		hrmtrip = Mhrmtripnormativeline.getCityCost(city.getC_City_ID(), "1");
					    index = hrmtrip.getCost();
			    		if(hrmtrip.get_ID() == 0){ 
			    			hrmtrip = Mhrmtripnormativeline.getCost(city.getC_Country_ID(), "1");
			    			index = hrmtrip.getCost();
			    		}
			    		  formula = new Formula(cellStart1.getC()+3, cellStart1.getR()+n, "K6*" + index +"*D" + j, borderStyle);
			    		  sheet.addCell(formula);
				    	
			    		//  is Manager
				    	  hrmtrip = Mhrmtripnormativeline.getCityCost(city.getC_City_ID(), "2");
				    	  index = hrmtrip.getCost();
				    		   //luxKZ = index * rate; 
				    	  formula = new Formula(cellStart1.getC()+4, cellStart1.getR()+n, "K6*" + index +"*D" + j, borderStyle);
				    	  sheet.addCell(formula);
				    	
				    	// is Stuff
					    	hrmtrip = Mhrmtripnormativeline.getCityCost(city.getC_City_ID(), "3");
				    		index = hrmtrip.getCost();
				    		   //standartKZ = index * rate;
				    		formula = new Formula(cellStart1.getC()+5, cellStart1.getR()+n, "K6*" + index +"*D" + j, borderStyle);
				    		sheet.addCell(formula);			    		  
			    	   }else{
			    		   	hrmtrip = Mhrmtripnormativeline.getCityCost(city.getC_City_ID(), "1");
			    		   	int perdiem = hrmtrip.getCost();
			    		    hrmtrip = Mhrmtripnormativeline.getCityCost(city.getC_City_ID(), "2");
				    		int lux = hrmtrip.getCost();
				    		hrmtrip = Mhrmtripnormativeline.getCityCost(city.getC_City_ID(), "3");
				    	    int standart = hrmtrip.getCost();
				    		if(hrmtrip.get_ID() == 0){ 
				    			hrmtrip = Mhrmtripnormativeline.getCost(city.getC_Country_ID(), "1");
				    			perdiem = hrmtrip.getCost();
				    			hrmtrip = Mhrmtripnormativeline.getCost(city.getC_Country_ID(), "2");
				    			lux = hrmtrip.getCost();
				    			hrmtrip = Mhrmtripnormativeline.getCost(city.getC_Country_ID(), "3");
				    			standart = hrmtrip.getCost();
				    		}
			    	       if(hrmtrip.getC_Currency().getISO_Code().equals("EUR")){
			    	    	   currency = Integer.parseInt(sheet.getCell("J6").getContents());
			    	    	   formula = new Formula(cellStart1.getC()+3, cellStart1.getR()+n, "J6*D" + j + "*" + perdiem, borderStyle);
			    	    	   sheet.addCell(formula);
			    	    	   //  is Manager
			    	    	   formula = new Formula(cellStart1.getC()+4, cellStart1.getR()+n, "J6*D" + j + "*" + lux, borderStyle);
			    	    	   sheet.addCell(formula);
				    	       // is Stuff
			    	    	   formula = new Formula(cellStart1.getC()+5, cellStart1.getR()+n, "J6*D" + j + "*" + standart, borderStyle);
			    	    	   sheet.addCell(formula);
			    	       }
			    	       if(hrmtrip.getC_Currency().getISO_Code().equals("USD")){
			    	    	   currency = Integer.parseInt(sheet.getCell("I6").getContents());
			    	       //
			    	      formula = new Formula(cellStart1.getC()+3, cellStart1.getR()+n, "I6*D"+ j +"*"+perdiem, borderStyle);
			    	      sheet.addCell(formula);
			    	       //  is Manager
			    	      formula = new Formula(cellStart1.getC()+4, cellStart1.getR()+n, "I6*D"+ j +"*"+lux, borderStyle);
			    	      sheet.addCell(formula);
			    	       // is Stuff
			    	      formula = new Formula(cellStart1.getC()+5, cellStart1.getR()+n, "I6*D"+ j +"*"+standart, borderStyle);
			    	      sheet.addCell(formula);
			    	       }
			    	   }
			    	n++;
			    j++;
		    	// shift the basement
	     	   sheet.insertRow(cellStart1.getR()+n);
	     
	    	 }

       }
    	   copy.write();
	       copy.close();
	       tableBook.close();
	       temporaryFile.delete();
       
	    // Open the file with the application associated
	       String fileName = fullPath.toString();
	       Util.launchFile(new File(fileName));
       				
       return Msg.translate(m_ctx, "Success");
	}
}
